﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Computer_Manager.DTO;

namespace Computer_Manager.DAL
{
    class DAL_order
    {
        DbProvider db = new DbProvider();
        SqlCommand cmd;

        public int order_insert(DTO_order dt)
        {
            db.Open();
            int ID = 0;
            cmd = new SqlCommand("order_Insert", db.getConn());
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter thamso = new SqlParameter("@ID", ID);
            thamso.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(thamso);
            cmd.Parameters.Add("@codes_order", SqlDbType.VarChar).Value = dt.codes_order;
            cmd.Parameters.Add("@date_create", SqlDbType.DateTime).Value = dt.date_create;
            cmd.Parameters.Add("@id_user", SqlDbType.Int).Value = dt.id_user;
            cmd.Parameters.Add("@id_cus", SqlDbType.Int).Value = dt.id_cus;
            cmd.Parameters.Add("@total_money", SqlDbType.Float).Value = dt.total_money;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = dt.status;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            return (int)thamso.Value;
        }
        public void order_Update(DTO_order dt)
        {
            db.Open();
            cmd = new SqlCommand("order_Update", db.getConn());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@id_order", SqlDbType.Int).Value = dt.id_order;
            cmd.Parameters.Add("@codes_order", SqlDbType.VarChar).Value = dt.codes_order;
            cmd.Parameters.Add("@id_user", SqlDbType.Int).Value = dt.id_user;
            cmd.Parameters.Add("@date_create", SqlDbType.DateTime).Value = dt.date_create;
            cmd.Parameters.Add("@id_cus", SqlDbType.Int).Value = dt.id_cus;
            cmd.Parameters.Add("@total_money", SqlDbType.Float).Value = dt.total_money;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = dt.status;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
        public void order_Delete(DTO_order dt)
        {
            db.Open();
            cmd = new SqlCommand("order_Delete", db.getConn());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@id_order", SqlDbType.Int).Value = dt.id_order;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            db.getConn().Close();
        }
        public DataTable GetAll()
        {
            SqlDataAdapter adapter = new SqlDataAdapter("order_SelectAll", db.getConn());
            DataTable table = new DataTable();
            adapter.Fill(table);
            adapter.Dispose();
            return table;
        }
        public DataTable GetByID(DTO_order dt)
        {
            cmd = new SqlCommand("order_GetByID", db.getConn());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@id_order", SqlDbType.Int).Value = dt.id_order;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable GetByIdCus(DTO_order dt)
        {
            cmd = new SqlCommand("order_GetByIdCus", db.getConn());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@id_cus", SqlDbType.Int).Value = dt.id_cus;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable GetByDay(DTO_order dt)
        {
            cmd = new SqlCommand("order_GetByDay", db.getConn());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@day", SqlDbType.Date).Value = dt.date_create;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable GetByMonth(DTO_order dt)
        {
            cmd = new SqlCommand("order_GetByMonth", db.getConn());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@month", SqlDbType.Date).Value = dt.date_create;// Convert.ToDateTime(dt.date_create).Month;
            cmd.Parameters.Add("@year", SqlDbType.Date).Value = dt.date_create;// Convert.ToDateTime(dt.date_create).Year;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable GetByYear(DTO_order dt)
        {
            cmd = new SqlCommand("order_GetByYear", db.getConn());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@year", SqlDbType.Date).Value = dt.date_create;// Convert.ToDateTime(dt.date_create).Year;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
    }
}
